-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Tidus
-- Create date: 26/8/2011
-- Description:	Nghiep vu
-- =============================================
USE QLNS
GO

/******************************************* LIST POSITION **************************************/
IF OBJECT_ID ('sp_ListPosition', 'p') IS NOT NULL
	DROP PROC sp_ListPosition
Go
CREATE PROCEDURE sp_ListPosition
	@Record int,
	@Page int,
	@Sort varchar(50),
	@flag bit
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		DECLARE @Sql varchar(1000);
		IF @Page = 0
			SET @Sql = 'SELECT Pos_Id, Pos_Name FROM Position ORDER BY '+@Sort+' ASC' 
		ELSE
			BEGIN
				DECLARE @Min varchar(3);
				DECLARE @Max varchar(3);
				SET @Min = CAST((@Record*(@Page-1)) AS varchar(3));
				SET @Max = CAST((@Record*@Page+1) AS varchar(3));
				IF @flag = 'true' -- true la theo chieu be -> lon 
					SET @Sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@Sort+' ASC) AS rownum, 
									* FROM Position) AS a WHERE rownum >'+@Min+' AND rownum < '+@Max+'';
				ELSE -- false la tu lon -> be
					SET @Sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@Sort+' DESC) AS rownum, 
									* FROM Position) AS a WHERE rownum >'+@Min+' AND rownum < '+@Max+'';
			END
		EXEC (@Sql)
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO
/*
EXEC sp_ListPosition 5, 1, Pos_Id, true
*/


/*********************** UPDATE Position ************************/
/******************** [2]UPDATE ********************/
IF OBJECT_ID ('sp_UpdatePos', 'p') IS NOT NULL
	DROP PROC sp_UpdatePos
Go
CREATE PROCEDURE sp_UpdatePos
	@Pos_Id int,
	@Pos_Name nvarchar(127),
	@Pos_Describe nvarchar(500),
	@UserUpdate int
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		--[0]--
		DECLARE @DateUpdate datetime;
		SET @DateUpdate = GETDATE();
		--[1]--
		UPDATE dbo.Position
			SET [Pos_Name] = @Pos_Name
			  ,[Pos_Describe] = @Pos_Describe
			  ,[UserUpdate] = @UserUpdate
			  ,[DateUpdate] = @DateUpdate
			WHERE Pos_Id  = @Pos_Id
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO
/*********************** END UPDATE Position ************************/

/*********************** INSERT Position ************************/

/******************** [2]INSERT ********************/
IF OBJECT_ID ('sp_InsertPos', 'p') IS NOT NULL
	DROP PROC sp_InsertPos
Go
CREATE PROCEDURE sp_InsertPos
	@Pos_Name nvarchar(127),
	@Pos_Describe nvarchar(500),
	@UserInsert int
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		--[0]--
		DECLARE @DateInsert datetime;
		SET @DateInsert = GETDATE();
		--[1]--
		INSERT INTO Position([Pos_Name], [Pos_Describe] ,[UserInsert], [DateInsert])
			VALUES(@Pos_Name, @Pos_Describe, @UserInsert, @DateInsert);
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO
/*********************** END INSERT Position ************************/